﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using DelonixRegiaHotel.Classes;


namespace DelonixRegiaHotel.DBManager
{
    class DelonixRegiaDB
    {

        //Room Availability 
        public static ArrayList GetAvailRooms(DateTime checkIn, DateTime checkOut, string smkRm)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select roomPrice,roomDetails,roomTypeName,r.roomNo from Room r inner join RoomType rt on rt.roomTypeID = r.roomTypeID where smkRoom = @smkRoom AND roomNo not in (select roomNo from CheckInOut where checkInTime >= @checkIn and checkInTime <= @checkOut or checkOutTime >= @checkIn and checkOutTime <= @checkOut)";
                comm.Parameters.AddWithValue("@smkRoom", smkRm);
                comm.Parameters.AddWithValue("@checkIn", checkIn);
                comm.Parameters.AddWithValue("@checkOut", checkOut);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room rm = new Room();
                    RoomType rmType = new RoomType();
                    rm.RoomNo = Convert.ToString(dr["roomNo"]);
                    rmType.RoomPrice = Convert.ToDecimal(dr["roomPrice"]);
                    rmType.RoomDetails = Convert.ToString(dr["roomDetails"]);
                    rmType.RoomTypeName = Convert.ToString(dr["roomTypeName"]);
                    result.Add(rm);
                    result.Add(rmType);
                }

                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static ArrayList GetOccupiedRooms(DateTime checkIn, DateTime checkOut, string smkRm)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select roomPrice,roomDetails,roomTypeName,r.roomNo from Room r inner join RoomType rt on rt.roomTypeID = r.roomTypeID where smkRoom = @smkRoom AND roomNo in (select roomNo from CheckInOut where checkInTime >= @checkIn and checkInTime <= @checkOut or checkOutTime >= @checkIn and checkOutTime <= @checkOut)";
                comm.Parameters.AddWithValue("@smkRoom", smkRm);
                comm.Parameters.AddWithValue("@checkIn", checkIn);
                comm.Parameters.AddWithValue("@checkOut", checkOut);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room rm = new Room();
                    RoomType rmType = new RoomType();
                    rm.RoomNo = Convert.ToString(dr["roomNo"]);
                    rmType.RoomPrice = Convert.ToDecimal(dr["roomPrice"]);
                    rmType.RoomDetails = Convert.ToString(dr["roomDetails"]);
                    rmType.RoomTypeName = Convert.ToString(dr["roomTypeName"]);
                    result.Add(rm);
                    result.Add(rmType);
                }

                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static ArrayList GetScheduleRooms(string smkRm)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select roomPrice,roomDetails,roomTypeName,r.roomNo from Room r inner join RoomType rt on rt.roomTypeID = r.roomTypeID where smkRoom = @smkRoom AND roomNo in (select roomNo from CheckInOut where roomStatus = 'Clean')";
                comm.Parameters.AddWithValue("@smkRoom", smkRm);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room rm = new Room();
                    RoomType rmType = new RoomType();
                    rm.RoomNo = Convert.ToString(dr["roomNo"]);
                    rmType.RoomPrice = Convert.ToDecimal(dr["roomPrice"]);
                    rmType.RoomDetails = Convert.ToString(dr["roomDetails"]);
                    rmType.RoomTypeName = Convert.ToString(dr["roomTypeName"]);
                    result.Add(rm);
                    result.Add(rmType);
                }

                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static ArrayList GetRoomDetails(String roomNo)
        {
            ArrayList list = new ArrayList();

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT rt.roomDetails, roomPrice, roomTypeName FROM RoomType rt INNER JOIN Room r on r.roomTypeID = rt.roomTypeID WHERE roomNo =@roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                //Execute SQL commmand and read data
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    //Store the result into arraylist
                    RoomType rt = new RoomType();
                    rt.RoomTypeName = (string)dr["roomTypeName"];
                    rt.RoomPrice = (decimal)dr["roomPrice"];
                    rt.RoomDetails = (string)dr["roomDetails"];
                    list.Add(rt);
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return list;
        }

        public static Customer getCustomerDetails(string nric)
        {
            Customer c = null;
            SqlConnection conn = null;
            try
            {
                //Connect database
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

                conn.Open(); //Start the connection / Log in to database

                //Prepare the SQL command and pass parameter into the command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Customer WHERE nric=@nric";
                comm.Parameters.AddWithValue("@nric", nric);

                //Execute the command and read the data
                SqlDataReader dr = comm.ExecuteReader();

                //While data is being read, store it into the Music object
                while (dr.Read())
                {
                    c = new Customer();
                    c.CustomerID = (string)dr["customerID"];
                    c.FirstName = (string)dr["firstName"];
                    c.LastName = (string)dr["lastName"];
                    c.HomeAddress = (string)dr["homeAddress"];
                    c.PostalCode = (int)dr["postalCode"];
                    c.Country = (string)dr["country"];
                    c.Dob = (DateTime)dr["dob"];
                    c.ContactNo = (int)dr["contactNo"];
                    c.Email = (string)dr["email"];
                    c.Nric = (string)dr["nric"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return c;
        } 

       

        //CheckIn
        public static int InsertCustomer(Customer cu)
        {
            int rowadded = 0;
            SqlConnection conn = null;
            try
            {
                //establish connection with DB
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Customer(customerID, firstName, lastName, nric, homeAddress, country, postalCode,  dob, contactNo, email) VALUES"
                    + "(@customerID, @firstName, @lastName, @nric, @homeAddress, @country, @postalCode, @dob, @contactNo, @email)";

                // comm.Parameters.AddWithValue("@volunteerID", vol.VolunteerID);
                comm.Parameters.AddWithValue("@customerID", cu.CustomerID);
                comm.Parameters.AddWithValue("@firstName", cu.FirstName);
                comm.Parameters.AddWithValue("@lastName", cu.LastName);
                comm.Parameters.AddWithValue("@nric", cu.Nric);
                comm.Parameters.AddWithValue("@homeAddress", cu.HomeAddress);
                comm.Parameters.AddWithValue("@country", cu.Country);
                comm.Parameters.AddWithValue("@postalcode", cu.PostalCode);
                comm.Parameters.AddWithValue("@dob", cu.Dob);
                comm.Parameters.AddWithValue("@contactNo", cu.ContactNo);
                comm.Parameters.AddWithValue("@email", cu.Email);

                //Execute SQL commmand and read data
                rowadded = comm.ExecuteNonQuery();


            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        public static CreditCardInfo getCustomerCardInfo(Int64 creditCardNo)
        {
            CreditCardInfo cci = null;
            SqlConnection conn = null;
            try
            {
                //Connect database
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

                conn.Open(); //Start the connection / Log in to database

                //Prepare the SQL command and pass parameter into the command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT creditCardExpDate FROM CreditCardInfo where creditCardNo = @creditCardNo";
                comm.Parameters.AddWithValue("@creditCardNo", creditCardNo);

                //Execute the command and read the data
                SqlDataReader dr = comm.ExecuteReader();

                //While data is being read, store it into the Music object
                while (dr.Read())
                {
                    cci = new CreditCardInfo();
                    cci.CreditCardExpDate = (DateTime)dr["creditCardExpDate"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cci;
        } 

        public static int InsertCheckInDetails(CheckInOut cio)
        {
            int rowadded = 0;
            SqlConnection conn = null;
            try
            {
                //establish connection with DB
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CheckInOut(checkID, deposit, balance, paymentType, checkInTime, checkOutTime, numOfAdult, numOfChild, numOfKey, roomRate, remarks, roomNo, customerID, numOfDays, roomStatus) VALUES"
                    + "(@checkID, @deposit, @balance, @paymentType, @checkInTime, @checkOutTime, @numOfAdult, @numOfChild, @numOfKey, @roomRate, @remarks, @roomNo, @customerID, @numOfDays, @roomStatus)";


                comm.Parameters.AddWithValue("@checkID", cio.Balance);
                comm.Parameters.AddWithValue("@deposit", cio.Deposit);
                comm.Parameters.AddWithValue("@balance", cio.Balance);
                comm.Parameters.AddWithValue("@paymentType", cio.PaymentType);
                comm.Parameters.AddWithValue("@checkInTime", cio.CheckInTime);
                comm.Parameters.AddWithValue("@checkOutTime", cio.CheckOutTime);
                comm.Parameters.AddWithValue("@numOfAdult", cio.NumOfAdult);
                comm.Parameters.AddWithValue("@numOfChild", cio.NumOfChild);
                comm.Parameters.AddWithValue("@numOfKey", cio.NumOfKey);
                comm.Parameters.AddWithValue("@roomRate", cio.RoomRate);
                comm.Parameters.AddWithValue("@remarks" ,cio.Remarks);
                comm.Parameters.AddWithValue("@roomNo", cio.RoomNo);
                comm.Parameters.AddWithValue("@customerID", cio.CustomerID);
                comm.Parameters.AddWithValue("@numOfDays", cio.NumOfDays);
                comm.Parameters.AddWithValue("@roomStatus", cio.RoomStatus);


                //Execute SQL commmand and read data
                rowadded = comm.ExecuteNonQuery();


            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }


        public static int InsertCreditCardInfo(CreditCardInfo card)
        {
            int rowadded = 0;
            SqlConnection conn = null;
            try
            {
                //establish connection with DB
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CreditCardInfo(creditCardNo, customerID, creditCardHolderName, creditCardExpDate) VALUES"
                    + "(@creditCardNo, @customerID, @creditCardHolderName, @creditCardExpDate)";

                // comm.Parameters.AddWithValue("@volunteerID", vol.VolunteerID);
                comm.Parameters.AddWithValue("@creditCardNo", card.CreditCardNo);
                comm.Parameters.AddWithValue("@customerID", card.CustomerID);
                comm.Parameters.AddWithValue("@creditCardHolderName" , card.CreditCardHolder);
                comm.Parameters.AddWithValue("@creditCardExpDate", card.CreditCardExpDate);


                //Execute SQL commmand and read data
                rowadded = comm.ExecuteNonQuery();


            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }


        public static bool CheckForCustomerNric(string nric)
        {
            bool result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                   ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * FROM Customer WHERE nric=@nric";
                comm.Parameters.AddWithValue("@nric", nric);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static bool CheckForCustomerID(string cuID)
        {
            bool result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                   ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * FROM Customer WHERE customerID=@customerID";
                comm.Parameters.AddWithValue("@customerID", cuID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        } 

        
        public static int UpdateCustomer(Customer Cu)
        {
            int rowupdated = 0;
            SqlConnection conn = null;
            try
            {
                //establish connection with DB
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = " UPDATE Customer SET firstName=@firstName, lastName=@lastName, homeAddress=@homeAddress, postalCode=@postalCode, country=@country, dob=@dob, contactNo=@contactNo, email=@email, nric=@nric where customerID=@customerID";
                comm.Parameters.AddWithValue("@customerID", Cu.CustomerID);
                comm.Parameters.AddWithValue("@firstName", Cu.FirstName);
                comm.Parameters.AddWithValue("@lastName", Cu.LastName);
                comm.Parameters.AddWithValue("@homeAddress", Cu.HomeAddress);
                comm.Parameters.AddWithValue("@postalCode", Cu.PostalCode);
                comm.Parameters.AddWithValue("@country", Cu.Country);
                comm.Parameters.AddWithValue("@dob", Cu.Dob);
                comm.Parameters.AddWithValue("@contactNo", Cu.ContactNo);
                comm.Parameters.AddWithValue("@email", Cu.Email);
                comm.Parameters.AddWithValue("@nric", Cu.Nric);
                //Execute SQL commmand and read data
                rowupdated = comm.ExecuteNonQuery();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }
        public static CheckInOut getAllCheckinInfo(string nric)
        {
            CheckInOut cio = null;
            SqlConnection conn = null;
            try
            {
                //Connect database
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

                conn.Open(); //Start the connection / Log in to database

                //Prepare the SQL command and pass parameter into the command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT checkID, balance, paymentType, checkInTime, checkOutTime, numOfAdult, numOfChild, numOfKey, roomRate, remarks, roomNo, cio.customerID, numOfDays, roomStatus  FROM CheckInOut cio inner join Customer c on c.customerID = cio.customerID where nric =(Select nric from Customer where nric=@nric) and roomStatus ='Booked'";
                comm.Parameters.AddWithValue("@nric", nric);

                //Execute the command and read the data
                SqlDataReader dr = comm.ExecuteReader();

                //While data is being read, store it into the Music object
                while (dr.Read())
                {
                    cio.CheckID = (string)dr["checkID"];
                    cio.Balance = (decimal)dr["balance"];
                    cio.PaymentType = (string)dr["paymentType"];
                    cio.CheckInTime = (DateTime)dr["checkInTime"];
                    cio.CheckOutTime = (DateTime)dr["checkOutTime"];
                    cio.NumOfAdult = (int)dr["numOfAdult"];
                    cio.NumOfChild = (int)dr["numOfChild"];
                    cio.NumOfKey = (int)dr["numOfKey"];
                    cio.RoomRate = (decimal)dr["roomRate"];
                    cio.Remarks = (string)dr["remarks"];
                    cio.RoomNo = (string)dr["roomNo"];
                    cio.CustomerID = (string)dr["customerID"];
                    cio.NumOfDays = (int)dr["numOfDays"];
                    cio.RoomStatus = (string)dr["roomStatus"];
                    
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cio;
        }


        //Advance Booking
        public static CheckInOut getBookingDetails(string roomNo)
        {
            CheckInOut cio = new CheckInOut();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT checkID, checkInTime, checkOutTime, numOfAdult, numOfChild, numOfDays,roomNo, deposit, balance, paymentType, numofKey, remarks, roomStatus FROM checkInOut WHERE roomNo = @roomNo AND roomStatus='Booked'";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    cio.CheckID = Convert.ToString(dr["checkID"]);
                    cio.CheckInTime = (DateTime)dr["checkInTime"];
                    cio.CheckOutTime = (DateTime)dr["checkOutTime"];
                    cio.NumOfAdult = (int)dr["numOfAdult"];
                    cio.NumOfChild = (int)dr["numOfChild"];
                    cio.NumOfDays = (int)dr["numOfDays"];
                    cio.RoomNo = (string)dr["roomNo"];
                    cio.Deposit = (decimal)dr["deposit"];
                    cio.Balance = (decimal)dr["balance"];
                    cio.PaymentType = (string)dr["paymentType"];
                    cio.NumOfKey = (int)dr["numOfKey"];
                    cio.Remarks = (string)dr["remarks"];
                    cio.RoomStatus = (string)dr["roomStatus"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cio;
        }
        public static ArrayList getRoomNo(string nric)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                //Connect database
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

                conn.Open(); //Start the connection / Log in to database

                //Prepare the SQL command and pass parameter into the command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select roomNo From CheckInOut cio inner join customer c on c.customerID = cio.customerID Where roomStatus ='Booked'";
                comm.Parameters.AddWithValue("@nric",nric);

                //Execute the command and read the data
                SqlDataReader dr = comm.ExecuteReader();

                //While data is being read, store it into the Music object
                while (dr.Read())
                {
                    CheckInOut cio = new CheckInOut();
                    cio.RoomNo = (string)dr["roomNo"];
                    result.Add(cio);

                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static RoomType getRoomInformation(string roomNo)
        {
            RoomType rt = new RoomType();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select roomTypename, roomPrice From RoomType rt inner join Room r on r.roomTypeID = rt.roomTypeID where roomNo = (Select roomNo from CheckInOut where roomNo =@roomNo)"; 
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    rt.RoomTypeName = (string)dr["roomTypeName"];
                    rt.RoomPrice = (decimal)dr["roomPrice"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rt;
        }
        public static int UpdateCheckInOut(CheckInOut cio)
        {
            int rowupdated = 0;
            SqlConnection conn = null;
            try
            {
                //establish connection with DB
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = " UPDATE CheckInOut SET roomStatus=@roomStatus , numOfKey=@numOfKey";
                
                comm.Parameters.AddWithValue("@roomStatus", cio.RoomStatus);
                comm.Parameters.AddWithValue("@numOfKey", cio.NumOfKey);
                //Execute SQL commmand and read data
                rowupdated = comm.ExecuteNonQuery();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        //Additional CheckInDetails
        public static ArrayList getAllItems ()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                //Connect database
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

                conn.Open(); //Start the connection / Log in to database

                //Prepare the SQL command and pass parameter into the command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Item";

                //Execute the command and read the data
                SqlDataReader dr = comm.ExecuteReader();

                //While data is being read, store it into the Music object
                while (dr.Read())
                {
                    Item i = new Item();
                    i.ItemName = Convert.ToString(dr["itemName"]);
                    i.UnitPrice = Convert.ToDecimal(dr["unitPrice"]);
                    result.Add(i);
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static ArrayList getAllunitSubTotal(string checkID)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                //Connect database
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

                conn.Open(); //Start the connection / Log in to database

                //Prepare the SQL command and pass parameter into the command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select unitSubTotal from CheckOutExtraCost where checkID=@checkID";
                comm.Parameters.AddWithValue("checkID", checkID);
                //Execute the command and read the data
                SqlDataReader dr = comm.ExecuteReader();

                //While data is being read, store it into the Music object
                while (dr.Read())
                {
                    CheckOutExtraCost coe = new CheckOutExtraCost();
                    coe.UnitSubTotal = (decimal)dr["unitSubTotal"];

                    result.Add(coe);
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static int InsertCheckOutExtraCost(CheckOutExtraCost co)
        {
            int rowadded = 0;
            SqlConnection conn = null;
            try
            {
                //establish connection with DB
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CheckOutExtraCost(itemName, checkID, quantity, unitSubTotal) VALUES"
                    + "(@itemName, @checkID, @quantity, @unitSubTotal)";

                // comm.Parameters.AddWithValue("@volunteerID", vol.VolunteerID);
                comm.Parameters.AddWithValue("@itemName", co.ItemName);
                comm.Parameters.AddWithValue("@checkID", co.CheckID);
                comm.Parameters.AddWithValue("@quantity", co.Quantity);
                comm.Parameters.AddWithValue("@unitSubTotal", co.UnitSubTotal);

                //Execute SQL commmand and read data
                rowadded = comm.ExecuteNonQuery();


            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }
        public static ArrayList GetcheckInDetails(string nric)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                //Connect database
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

                conn.Open(); //Start the connection / Log in to database

                //Prepare the SQL command and pass parameter into the command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select checkID, roomNo, cu.customerID , firstName, lastName FROM CheckInOut cio inner join Customer cu on cu.customerID = cio.customerID WHERE nric =@nric";
                comm.Parameters.AddWithValue("@nric", nric);
                //Execute the command and read the data
                SqlDataReader dr = comm.ExecuteReader();

                //While data is being read, store it into the Music object
                while (dr.Read())
                {
                    Customer cu = new Customer();
                    CheckInOut cio = new CheckInOut();
                    cio.CheckID = Convert.ToString(dr["checkID"]);
                    cio.RoomNo = Convert.ToString(dr["roomNo"]);
                    cu.CustomerID = Convert.ToString(dr["customerID"]);
                    cu.FirstName = Convert.ToString(dr["firstName"]);
                    cu.LastName = Convert.ToString(dr["lastName"]);
                    result.Add(cio);
                    result.Add(cu);
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }


        public static CheckInOut getCheckInID(string roomNo)
        {
            CheckInOut cio = new CheckInOut();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT checkID FROM checkInOut WHERE roomNo = @roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    
                    cio.CheckID = Convert.ToString(dr["checkID"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cio;
        }

        public static bool CheckForExistingItem(string itemName ,string checkID)
        {
            bool result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                   ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * FROM CheckOutExtraCost WHERE itemName = @itemName AND checkID = @checkID";
                comm.Parameters.AddWithValue("@itemName", itemName);
                comm.Parameters.AddWithValue("@checkID", checkID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static bool CheckForExistingCheckID(string checkID)
        {
            bool result = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                   ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * FROM CheckOutExtraCost WHERE checkID = @checkID";
                comm.Parameters.AddWithValue("@checkID", checkID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    result = true;
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }
        public static int UpdateExistingItems(CheckOutExtraCost coe)
        {
            int rowupdated = 0;
            SqlConnection conn = null;
            try
            {
                //establish connection with DB
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = " UPDATE CheckOutExtraCost SET quantity=@quantity, unitSubTotal=@unitSubTotal where checkID=@checkID and itemName = @itemName";
                comm.Parameters.AddWithValue("@checkID", coe.CheckID);
                comm.Parameters.AddWithValue("@itemName", coe.ItemName);
                comm.Parameters.AddWithValue("@quantity", coe.Quantity);
                comm.Parameters.AddWithValue("@unitSubTotal", coe.UnitSubTotal);
 
                //Execute SQL commmand and read data
                rowupdated = comm.ExecuteNonQuery();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }
        public static CheckOutExtraCost getItemsFromChkIn(string checkID , string itemName)
        {
            CheckOutExtraCost c = new CheckOutExtraCost();
            SqlConnection conn = null;
            try
            {
                //Connect database
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

                conn.Open(); //Start the connection / Log in to database

                //Prepare the SQL command and pass parameter into the command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT itemName, quantity, unitSubTotal FROM CheckOutExtraCost WHERE checkID=@checkID AND itemName=@itemName";
                comm.Parameters.AddWithValue("@checkID", checkID);
                comm.Parameters.AddWithValue("@itemName", itemName);

                //Execute the command and read the data
                SqlDataReader dr = comm.ExecuteReader();

                //While data is being read, store it into the Music object
                while (dr.Read())
                {
                //    c.CheckID = (string)dr["checkID"];
                    c.ItemName = (string)dr["itemName"];
                    c.Quantity = (int)dr["quantity"];
                    c.UnitSubTotal = (decimal)dr["unitSubTotal"];
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return c;
        }
  

        //checkout
        public static ArrayList getAllCheckOutExtraCost(string checkID)
        {
            ArrayList result = new ArrayList();
        
            SqlConnection conn = null;
            try
            {
                //Connect database
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

                conn.Open(); //Start the connection / Log in to database

                //Prepare the SQL command and pass parameter into the command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT itemName, quantity, unitSubTotal  FROM CheckOutExtraCost  where checkID= @checkID";
                comm.Parameters.AddWithValue("checkID", checkID);
                //Execute the command and read the data
                SqlDataReader dr = comm.ExecuteReader();

                //While data is being read, store it into the Music object
                while (dr.Read())
                {
                    CheckOutExtraCost coe = new CheckOutExtraCost();

                    coe.ItemName = (string)dr["itemName"];
                    coe.Quantity = (int)dr["quantity"];
                    coe.UnitSubTotal = (decimal)dr["unitSubTotal"];
                    result.Add(coe);

                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static CheckInOut getAllCheckInDetails(string roomNo)
        {
            CheckInOut cio = new CheckInOut();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT checkID,balance, checkInTime, checkOutTime, paymentType, numOfAdult, numOfChild, numOfKey, roomStatus, extraCostTotal FROM checkInOut WHERE roomNo = @roomNo";
                comm.Parameters.AddWithValue("@roomNo", roomNo);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    cio.CheckID = Convert.ToString(dr["checkID"]);
                    cio.Balance = (decimal)dr["balance"];
                    cio.CheckInTime = (DateTime)dr["checkInTime"];
                    cio.CheckOutTime = (DateTime)dr["checkOutTime"];
                    cio.PaymentType = (string)dr["paymentType"];
                    cio.NumOfAdult = (int)dr["numOfAdult"];
                    cio.NumOfChild = (int)dr["numOfChild"];
                    cio.NumOfKey = (int)dr["numOfKey"];
                    cio.RoomStatus = (string)dr["roomStatus"];
                    cio.ExtraCostTotal = (decimal)dr["extraCostTotal"];
                    

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cio;
        }

        public static int UpdateTotalCost(CheckInOut cio)
        {
            int rowupdated = 0;
            SqlConnection conn = null;
            try
            {
                //establish connection with DB
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = " UPDATE CheckInOut SET extraCostTotal=@extraCostTotal where checkID=@checkID";
                comm.Parameters.AddWithValue("@checkID", cio.CheckID);
                comm.Parameters.AddWithValue("@extraCostTotal", cio.ExtraCostTotal);
                //Execute SQL commmand and read data
                rowupdated = comm.ExecuteNonQuery();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        public static int UpdateRoomStatus(CheckInOut cio)
        {
            int rowupdated = 0;
            SqlConnection conn = null;
            try
            {
                //establish connection with DB
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = " UPDATE CheckInOut SET roomStatus=@roomStatus where checkID=@checkID";
                comm.Parameters.AddWithValue("@checkID", cio.CheckID);
                comm.Parameters.AddWithValue("@roomStatus", cio.RoomStatus);
                //Execute SQL commmand and read data
                rowupdated = comm.ExecuteNonQuery();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowupdated;
        }

        public static CheckInOut getCurrentRoomStatus(string checkID)
        {
            CheckInOut cio = new CheckInOut();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomStatus FROM checkInOut WHERE checkID = @checkID";
                comm.Parameters.AddWithValue("@roomStatus", checkID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    cio.RoomStatus = (string)dr["roomStatus"];


                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cio;
        }




        //Invoice
        public static int InsertIntoInovoice(Invoice invoice)
        {
            int rowadded = 0;
            SqlConnection conn = null;
            try
            {
                //establish connection with DB
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open(); //open connection with DB

                //Prepare SQL command and pass parameters
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Invoice(invoiceID, durationOfStay, totalAmount, invoiceDate, checkID) VALUES"
                    + "(@invoiceID, @durationOfStay, @totalAmount, @invoiceDate, @checkID)";

                // comm.Parameters.AddWithValue("@volunteerID", vol.VolunteerID);
                comm.Parameters.AddWithValue("@invoiceID", invoice.InvoiceID);
                comm.Parameters.AddWithValue("@durationOfStay", invoice.DurationOfStay);
                comm.Parameters.AddWithValue("@totalAmount", invoice.TotalAmount);
                comm.Parameters.AddWithValue("@checkID", invoice.CheckID);

                //Execute SQL commmand and read data
                rowadded = comm.ExecuteNonQuery();


            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowadded;
        }

        public static CheckInOut getInvoiceDetails(string checkID)
        {
            CheckInOut cio = new CheckInOut();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT roomNo, balance, checkInTime, checkOutTime, paymentType, numOfAdult, numOfChild, numOfKey, roomStatus, extraCostTotal, numOfDays FROM checkInOut WHERE checkID = @checkID";
                comm.Parameters.AddWithValue("@checkID", checkID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                 //   cio.CheckID = Convert.ToString(dr["checkID"]);
                   
                    cio.RoomNo = (string)dr["roomNo"];
                    cio.Balance = (decimal)dr["balance"];
                    cio.CheckInTime = (DateTime)dr["checkInTime"];
                    cio.CheckOutTime = (DateTime)dr["checkOutTime"];
                    cio.PaymentType = (string)dr["paymentType"];
                    cio.NumOfAdult = (int)dr["numOfAdult"];
                    cio.NumOfChild = (int)dr["numOfChild"];
                    cio.NumOfKey = (int)dr["numOfKey"];
                    cio.RoomStatus = (string)dr["roomStatus"];
                    cio.ExtraCostTotal = (decimal)dr["extraCostTotal"];
                    cio.NumOfDays = (int)dr["numOfDays"];


                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return cio;
        }

        public static RoomType getRoomPrice(string checkID)
        {
            RoomType rt = new RoomType();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select roomPrice, roomTypeName from RoomType where roomTypeID = (Select roomTypeID from Room r inner join CheckInOut cio on r.roomNo = cio.roomNo where checkID =@checkID);";
                comm.Parameters.AddWithValue("@checkID", checkID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    rt.RoomPrice = (decimal)dr["roomPrice"];
                    rt.RoomTypeName = (string)dr["roomTypeName"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rt;
        }

        public static Customer getCustomerName(string checkID)
        {
            Customer c = new Customer();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select firstName, lastName from Customer c inner join CheckInOut cio on c.customerID = cio.customerID where cio.checkID = (Select checkID from CheckInOut where checkID = @checkID)";
                comm.Parameters.AddWithValue("@checkID", checkID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    //   cio.CheckID = Convert.ToString(dr["checkID"]);


                    c.FirstName = (string)dr["firstName"];
                    c.LastName = (string)dr["lastName"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return c;
        }

    }
}
